package com.erp.app.admin.systemMenu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import com.erp.app.admin.systemMenu.beans.SystemMenuGroupVO;
import com.erp.app.admin.systemMenu.ejb.SystemMenuGroupPK;
import com.erp.framework.exceptions.CreateRecordException;
import com.erp.framework.exceptions.DeleteRecordException;
import com.erp.framework.exceptions.FindRecordException;
import com.erp.framework.exceptions.ModifyRecordException;
import com.erp.framework.model.dao.SimplePageEditStyleDao;
import com.erp.framework.model.pk.SimpleBasePk;
import com.erp.framework.model.vo.ChildList;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.util.logger.Logger;

public class SystemMenuGroupDAO extends SimplePageEditStyleDao {

	protected String sqlPurge(){
        return sqlDelete();
    }

    protected void setValuesForPurge(PreparedStatement pStmt, Object obj) throws SQLException {
        setValuesForDelete(pStmt, obj);
    }

    protected int getTablesInEffect() {
        return 1;
        // the project need not the background image so no need this part!!!!
        // return 2;
    }

    protected String sqlInsert() {
        // dummy method
        return "";
    }

    protected String sqlInsert(short j) {

        if (j == 0) {
            return "INSERT INTO T_MENU_GROUP (" + "COMPANY_CODE, GROUP_ID, MENU_ID, PARENT_MENU_ID, MENU_NAME, MENU_TYPE"
                    + ", MENU_ORDER, MENU_URL, MENU_ICON, ROOT_MENU_ID, STATUS, CONTENT_TYPE, CREATE_USER, CREATE_DATE" + ") "
                    + "VALUES (?,?,?,?,?,?,?,?,?,?,?,?,sysdate)";
        } else {
            return "INSERT INTO T_MENU_GROUP_IMAGE ("
                    + "COMPANY_CODE, GROUP_ID, MENU_ID, MENU_IMAGE, CONTENT_TYPE, CREATE_USER, CREATE_DATE) "
                    + "VALUES (?,?,?,?,?,?,sysdate)";
        }
    }

    protected void setValuesForInsert(PreparedStatement pStmt, Object obj, short j) throws SQLException {
        SystemMenuGroupVO menuGroupVO = (SystemMenuGroupVO) obj;
        int i = 1;

        if (j == 0) {
            pStmt.setString(i++, menuGroupVO.getCompanyCode());
            pStmt.setString(i++, menuGroupVO.getGroupId());
            pStmt.setString(i++, menuGroupVO.getMenuId());
            setNullableString(pStmt, i++, menuGroupVO.getParentMenuId());
            setNullableString(pStmt, i++, menuGroupVO.getMenuName());
            setNullableString(pStmt, i++, menuGroupVO.getMenuType());
            setNullableBigDecimal(pStmt, i++, menuGroupVO.getMenuOrder());
            setNullableString(pStmt, i++, menuGroupVO.getMenuUrl());
            setNullableBytes(pStmt, i++, menuGroupVO.getMenuIcon());
            setNullableString(pStmt, i++, menuGroupVO.getRootMenuId());
            setNullableString(pStmt, i++, menuGroupVO.getStatus());
            setNullableString(pStmt, i++, menuGroupVO.getIconContentType());
            setNullableString(pStmt, i++, menuGroupVO.getCreateUser());
        } else {
            pStmt.setString(i++, menuGroupVO.getCompanyCode());
            pStmt.setString(i++, menuGroupVO.getGroupId());
            pStmt.setString(i++, menuGroupVO.getMenuId());
            setNullableBytes(pStmt, i++, menuGroupVO.getMenuImage());
            setNullableString(pStmt, i++, menuGroupVO.getImageContentType());
            setNullableString(pStmt, i++, menuGroupVO.getCreateUser());
        }
    }

    public void insert(SimpleBaseVo bizModel, Connection conn) throws CreateRecordException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {

            for (short i = 0; i < getTablesInEffect(); i++) {

                pStmt = conn.prepareStatement(sqlInsert(i));
                setValuesForInsert(pStmt, bizModel, i);

                int count = pStmt.executeUpdate();
                if (count != 1)
                    throw new SQLException("Unable to insert " + bizModel.getPrimaryKey());
            }
        } catch (SQLException sqe) {
            System.err.println("Error in SalesContractHdrDAO.insert(): " + sqe.getMessage());
            throw new CreateRecordException(sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException sqe) {
                System.err.println("Error in SalesContractHdrDAO.insert() closing rs:" + sqe.getMessage());
            }

            try {
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
                System.err.println("Error in SalesContractHdrDAO.insert() closing stmt:" + sqe.getMessage());
            }
        }
    }

    protected void setValuesForInsert(PreparedStatement pStmt, Object obj) throws SQLException {
        // dummy method
    }

    public void update(SimpleBaseVo bizModel, Connection conn) throws ModifyRecordException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            for (short i = 0; i < getTablesInEffect(); i++) {

                pStmt = conn.prepareStatement(sqlUpdate(i));
                setValuesForModify(pStmt, bizModel, i);
                int count = pStmt.executeUpdate();
                if (count != 1) {
                    System.out.println("SystemMenu Rows updated = " + count);
                    throw new SQLException("Unable to update " + bizModel.getPrimaryKey());
                }
            }
        } catch (SQLException sqe) {
            throw new ModifyRecordException(sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
                System.err.println("Error closing stmt: " + sqe.getMessage());
            }
        }
    }

    protected String sqlUpdate() {
        // dummy method
        return "";
    }

    protected String sqlUpdate(short j) {
        if (j == 0) {
            String sql = "UPDATE T_MENU_GROUP SET "
                    + "PARENT_MENU_ID=?, MENU_NAME=?, MENU_TYPE=?, MENU_ORDER=?, MENU_URL=?, MENU_ICON=?, ROOT_MENU_ID=?, STATUS=?, CONTENT_TYPE=?"
                    + ", UPDATE_USER=?, UPDATE_DATE=sysdate " + "WHERE COMPANY_CODE = ? " + "AND GROUP_ID = ? "
                    + "AND MENU_ID = ? " + "AND (UPDATE_DATE IS NULL OR UPDATE_DATE = ?) ";
            return sql;
        } else {
            String sql = "UPDATE T_MENU_GROUP_IMAGE SET " + "MENU_IMAGE=?, CONTENT_TYPE=?, UPDATE_USER=?, UPDATE_DATE=sysdate "
                    + "WHERE COMPANY_CODE = ? " + "AND GROUP_ID = ? " + "AND MENU_ID = ? ";
            // TODO: comment the below for a bug, in the future, must create a
            // vo for t_menu_group_image
            // +"AND (UPDATE_DATE IS NULL OR UPDATE_DATE = ?) ";

            return sql;
        }
    }

    protected void setValuesForModify(PreparedStatement pStmt, Object obj, short j) throws SQLException {
        SystemMenuGroupVO menuGroupVO = (SystemMenuGroupVO) obj;
        int i = 1;
        if (j == 0) {
            setNullableString(pStmt, i++, menuGroupVO.getParentMenuId());
            setNullableString(pStmt, i++, menuGroupVO.getMenuName());
            setNullableString(pStmt, i++, menuGroupVO.getMenuType());
            setNullableBigDecimal(pStmt, i++, menuGroupVO.getMenuOrder());
            setNullableString(pStmt, i++, menuGroupVO.getMenuUrl());
            setNullableBytes(pStmt, i++, menuGroupVO.getMenuIcon());
            setNullableString(pStmt, i++, menuGroupVO.getRootMenuId());
            setNullableString(pStmt, i++, menuGroupVO.getStatus());
            setNullableString(pStmt, i++, menuGroupVO.getIconContentType());
            setNullableString(pStmt, i++, menuGroupVO.getUpdateUser());
            pStmt.setString(i++, menuGroupVO.getCompanyCode());
            pStmt.setString(i++, menuGroupVO.getGroupId());
            pStmt.setString(i++, menuGroupVO.getMenuId());
            setNullableTimestamp(pStmt, i++, menuGroupVO.getUpdateDate());
        } else {
            setNullableBytes(pStmt, i++, menuGroupVO.getMenuImage());
            setNullableString(pStmt, i++, menuGroupVO.getImageContentType());
            setNullableString(pStmt, i++, menuGroupVO.getUpdateUser());
            pStmt.setString(i++, menuGroupVO.getCompanyCode());
            pStmt.setString(i++, menuGroupVO.getGroupId());
            pStmt.setString(i++, menuGroupVO.getMenuId());
            // TODO: comment the below for a bug, in the future, must create a
            // vo for t_menu_group_image
            // setNullableTimestamp(pStmt, i++, menuGroupVO.getUpdateDate());
        }
    }

    protected void setValuesForModify(PreparedStatement pStmt, Object obj) throws SQLException {
        // dummy method
    }

    public void remove(SimpleBaseVo bizModel, Connection conn) throws DeleteRecordException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        try {

            for (short i = 0; i < getTablesInEffect(); i++) {

                pStmt = conn.prepareStatement(sqlDelete(i));
                setValuesForDelete(pStmt, bizModel, i);

                int count = pStmt.executeUpdate();
                if (count != 1)
                    throw new SQLException("Unable to delete " + bizModel.getPrimaryKey());
            }
        } catch (SQLException sqe) {
            throw new DeleteRecordException(sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
                System.err.println("Error closing stmt: " + sqe.getMessage());
            }
        }
    }

    protected String sqlDelete() {
        // dummy method
        return "";
    }

    protected String sqlDelete(short j) {
        if (j == 0) {
            return "DELETE FROM T_MENU_GROUP" + " WHERE COMPANY_CODE = ? " + " AND GROUP_ID = ? " + " AND MENU_ID = ? ";
        } else {
            return "DELETE FROM T_MENU_GROUP_IMAGE" + " WHERE COMPANY_CODE = ? " + " AND GROUP_ID = ? " + " AND MENU_ID = ? ";
        }

    }

    protected void setValuesForDelete(PreparedStatement pStmt, Object obj, short j) throws SQLException {
        SystemMenuGroupVO menuGroupVO = (SystemMenuGroupVO) obj;
        int i = 1;
        pStmt.setString(i++, menuGroupVO.getCompanyCode());
        pStmt.setString(i++, menuGroupVO.getGroupId());
        pStmt.setString(i++, menuGroupVO.getMenuId());

    }

    protected void setValuesForDelete(PreparedStatement pStmt, Object obj) throws SQLException {
        // dummy method

    }

    protected String sqlFind(short j) {
        if (j == 0) {
            return "SELECT COMPANY_CODE, GROUP_ID, MENU_ID, PARENT_MENU_ID, MENU_NAME, MENU_TYPE"
                    + ", MENU_ORDER, MENU_URL, MENU_ICON, ROOT_MENU_ID, STATUS, CONTENT_TYPE, CREATE_USER, CREATE_DATE" + ", UPDATE_USER, UPDATE_DATE "
                    + "FROM T_MENU_GROUP " + "WHERE COMPANY_CODE = ? " + "AND GROUP_ID = ? " + "AND MENU_ID = ? ";
        } else {
            return "SELECT COMPANY_CODE, GROUP_ID, MENU_ID, " + " MENU_IMAGE, CONTENT_TYPE, "
                    + " CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE " + " FROM T_MENU_GROUP_IMAGE "
                    + " WHERE COMPANY_CODE = ? " + " AND GROUP_ID = ? " + " AND MENU_ID = ? ";
        }

    }

    protected String sqlFind() {
        // dummy method
        return "";
    }

    protected void setValuesForFind(PreparedStatement pStmt, Object obj, short j) throws SQLException {
        SystemMenuGroupPK pk = (SystemMenuGroupPK) obj;
        int i = 1;
        pStmt.setString(i++, pk.getCompanyCode());
        pStmt.setString(i++, pk.getGroupId());
        pStmt.setString(i++, pk.getMenuId());
    }

    public void setValuesForFind(PreparedStatement pStmt, Object obj) throws SQLException {
        // dummy method
    }

    protected String sqlList() {
        return "";
    }

    protected void setValuesForList(PreparedStatement pStmt, Object obj) throws SQLException {
    }

    public Collection findByCriteria(Object obj, Connection conn) {
        // set the search criteria class
        SystemMenuGroupSrchCriteria criteria = (SystemMenuGroupSrchCriteria) obj;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList rslt = new ArrayList();

        try {
            String query = "SELECT COMPANY_CODE, GROUP_ID, MENU_ID, PARENT_MENU_ID, MENU_NAME, MENU_TYPE"
                    + ", MENU_ORDER, MENU_URL, MENU_ICON, ROOT_MENU_ID, STATUS, CONTENT_TYPE, CREATE_USER, CREATE_DATE" + ", UPDATE_USER, UPDATE_DATE "
                    + " FROM T_MENU_GROUP " + toWhereClause(criteria)
                    + " ORDER BY GROUP_ID, PARENT_MENU_ID, MENU_TYPE, MENU_ORDER, MENU_NAME ";

            super.logSqlQuery(query);
            stmt = conn.createStatement();
            rs = stmt.executeQuery(query);
            if (rs != null) {
                while (rs.next()) {
                    rslt.add((SystemMenuGroupVO) getResultSetValues(rs, null, (short) 0));
                }
            }
        } catch (Exception e) {
            Logger.logExceptions(this, e);
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (Exception e) {
                Logger.logExceptions(this, e);
            }
            try {
                if (stmt != null)
                    stmt.close();
            } catch (Exception e) {
                Logger.logExceptions(this, e);
            }
        }
        return rslt;
    }

    private String toWhereClause(SystemMenuGroupSrchCriteria criteria) {
        StringBuffer whereClause = new StringBuffer();

        if (criteria.getSrchCompanyCode() != null) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append("COMPANY_CODE = '" + criteria.getSrchCompanyCode().trim().toUpperCase() + "' ");
        }
        if (criteria.getSrchMenuId() != null) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append("MENU_ID = '" + criteria.getSrchMenuId().trim().toUpperCase() + "' ");
        }
        if (criteria.getSrchGroupId() != null) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append("GROUP_ID = '" + criteria.getSrchGroupId().trim().toUpperCase() + "' ");
        }
        if (criteria.getSrchMenuName() != null) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append("UPPER(MENU_NAME) like '%" + criteria.getSrchMenuName().trim().toUpperCase() + "%' ");
        }
        if (criteria.getSrchMenuType() != null) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append("MENU_TYPE = '" + criteria.getSrchMenuType().trim().toUpperCase() + "' ");
        }
        if (criteria.getSrchParentMenuId() != null) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append("PARENT_MENU_ID = '" + criteria.getSrchParentMenuId().trim().toUpperCase() + "' ");
        }
        if (criteria.getSrchStatus() != null) {
            whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
            whereClause.append("STATUS = '" + criteria.getSrchStatus().trim().toUpperCase() + "' ");
        }

        return whereClause.toString();
    }

    protected SimpleBaseVo getResultSetValues(ResultSet rs, SimpleBaseVo model, short j) throws SQLException {
        SystemMenuGroupVO menuGroupVO = model == null ? new SystemMenuGroupVO() : (SystemMenuGroupVO) model;
        try {
            menuGroupVO.setCompanyCode(rs.getString("COMPANY_CODE"));
            menuGroupVO.setGroupId(rs.getString("GROUP_ID"));
            menuGroupVO.setMenuId(rs.getString("MENU_ID"));
            if (j == 0) {
                menuGroupVO.setParentMenuId(rs.getString("PARENT_MENU_ID"));
                menuGroupVO.setMenuName(rs.getString("MENU_NAME"));
                menuGroupVO.setMenuType(rs.getString("MENU_TYPE"));
                menuGroupVO.setMenuOrder(rs.getBigDecimal("MENU_ORDER"));
                menuGroupVO.setMenuUrl(rs.getString("MENU_URL"));
                menuGroupVO.setMenuIcon(getBytes(rs, "MENU_ICON"));
                menuGroupVO.setRootMenuId(rs.getString("ROOT_MENU_ID"));
                menuGroupVO.setStatus(rs.getString("STATUS"));
                menuGroupVO.setIconContentType(rs.getString("CONTENT_TYPE"));
            } else {
                menuGroupVO.setMenuImage(getBytes(rs, "MENU_IMAGE"));
                menuGroupVO.setImageContentType(rs.getString("CONTENT_TYPE"));
            }
            menuGroupVO.setCreateUser(rs.getString("CREATE_USER"));
            menuGroupVO.setCreateDate(rs.getTimestamp("CREATE_DATE"));
            menuGroupVO.setUpdateUser(rs.getString("UPDATE_USER"));
            menuGroupVO.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));
        } catch (Exception e) {
            Logger.logExceptions(this, e);
        }
        return menuGroupVO;
    }

    protected SimpleBaseVo getResultSetValues(ResultSet rs) {
        // dummy method
        return null;
    }

    public final ChildList selectByParent(SimpleBaseVo parent, Class childType, Connection conn) throws FindRecordException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        ChildList rslt = new ChildList(childType);

        try {
            for (short i = 0, j; i < getTablesInEffect(); i++) {

                pStmt = conn.prepareStatement(sqlList(i));
                setValuesForList(pStmt, parent.getPrimaryKey(), i);

                j = 0;
                rs = pStmt.executeQuery();

                while (rs.next()) {
                    if (i == 0) {
                        SimpleBaseVo childVO = (SimpleBaseVo) getResultSetValues(rs, null, i);
                        childVO.setParent(parent);
                        childVO.setIsExisting(true);
                        childVO.setIsDirty(false);

                        rslt.add(childVO);
                    } else {
                        SimpleBaseVo childVO = (SimpleBaseVo) rslt.get(j);
                        if (childVO != null) {
                            childVO = (SimpleBaseVo) getResultSetValues(rs, childVO, i);
                            rslt.set(j, childVO);
                        }
                        j++;
                    }
                }
            }
        } catch (SQLException sqe) {
            System.err.println("SystemMenuGroupDAO.selectByParent(): " + (sqe.getMessage()));
            throw new FindRecordException(sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException sqe) {
                System.err.println("SystemMenuGroupDAO.selectByParent() [" + childType.getName() + "] closing rs: "
                        + sqe.getMessage());
            }

            try {
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
                System.err.println("SystemMenuGroupDAO.selectByParent() [" + childType.getName() + "] closing stmt: "
                        + sqe.getMessage());
            }
        }
        return rslt;
    }

    protected String sqlList(short i) {
        if (getTablesInEffect() == 1) {
            return sqlList();
        } else {
            throw new UnsupportedOperationException("SystemMenuGroupDAO.sqlList was not implemented.");
        }
    }

    protected void setValuesForList(PreparedStatement pStmt, Object obj, short i) throws SQLException {

        if (getTablesInEffect() == 1) {
            setValuesForList(pStmt, obj);
        } else {
            throw new UnsupportedOperationException("SystemMenuGroupDAO.setValuesForList was not implemented.");
        }
    }

    public SimpleBaseVo findByPrimaryKey(Object obj, Connection conn) {
        // dummy method
        SimpleBaseVo rslt = null;
        try {
            rslt = findByPrimaryKey((SimpleBasePk) obj, conn);
        } catch (Exception e) {
            Logger.logExceptions(this, e);
        }
        return rslt;
    }

    public final SimpleBaseVo findByPrimaryKey(SimpleBasePk pk, Connection conn) throws FindRecordException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;
        SimpleBaseVo model = null;

        try {
            for (short i = 0; i < getTablesInEffect(); i++) {

                pStmt = conn.prepareStatement(sqlFind(i));
                setValuesForFind(pStmt, pk, i);

                rs = pStmt.executeQuery();

                if (rs.next()) {
                    if (i == 0) {
                        model = (SimpleBaseVo) getResultSetValues(rs, null, i);
                    } else {
                        model = (SimpleBaseVo) getResultSetValues(rs, model, i);
                    }
                }
            }
        } catch (SQLException sqe) {
            Logger.logExceptions(this, sqe);
            throw new FindRecordException(sqe.getMessage());
        } finally {
            try {
                if (rs != null)
                    rs.close();
            } catch (SQLException sqe) {
                System.err.println("SystemMenuGroupDAO.findByPrimaryKey() [" + pk.getClass().getName() + "] closing rs: "
                        + sqe.getMessage());
            }

            try {
                if (pStmt != null)
                    pStmt.close();
            } catch (SQLException sqe) {
                System.err.println("SystemMenuGroupDAO.findByPrimaryKey() [" + pk.getClass().getName() + "] closing stmt: "
                        + sqe.getMessage());
            }
        }
        return model;
    }

    protected String sqlFindOuterJoin(short j) {
        if (j == 0) {
            return "SELECT COMPANY_CODE, GROUP_ID, MENU_ID, PARENT_MENU_ID, MENU_NAME, MENU_TYPE"
                    + ", MENU_ORDER, MENU_URL, MENU_ICON, ROOT_MENU_ID, STATUS, CONTENT_TYPE, CREATE_USER, CREATE_DATE" + ", UPDATE_USER, UPDATE_DATE "
                    + "FROM T_MENU_GROUP " + "WHERE COMPANY_CODE = ? " + "AND GROUP_ID = ? " + "AND MENU_ID = ? ";
        } else {
            return "SELECT COMPANY_CODE, GROUP_ID, MENU_ID, " + " MENU_IMAGE, CONTENT_TYPE, "
                    + " CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE " + " FROM T_MENU_GROUP_IMAGE "
                    + " WHERE COMPANY_CODE = ? " + " AND GROUP_ID = ? " + " AND MENU_ID = ? ";
        }

    }

    public ChildList selectByParent(SimpleBaseVo parent, Connection conn) throws FindRecordException {
        // dummy method
        return null;
    }
}
